package com.ai.controller.reviewed;

import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.servlet.ModelAndView;

import com.ai.common.controller.BaseController;
import com.ai.common.exception.AlikAssert;
import com.ai.common.model.Pagination;
import com.ai.common.model.Result;
import com.ai.mmd.reviewed.dto.SbhzinfoDTO;
import com.ai.mmd.reviewed.dto.SbmxinfoDTO;
import com.ai.mmd.reviewed.dto.StatisticsDTO;
import com.ai.mmd.reviewed.vo.PreviewinfoVO;
import com.ai.mmd.reviewed.vo.SbhzinfoVO;
import com.ai.mmd.reviewed.vo.SbmxinfoVO;
import com.ai.mmd.reviewed.vo.StatisticsVO;
import com.ai.mmd.reviewed.vo.YhxxVO;
import com.ai.service.reviewed.model.SbhzinfoModel;
import com.ai.service.reviewed.model.SbmxinfoModel;
import com.ai.service.reviewed.service.IJzxslinfoService;
import com.ai.service.reviewed.service.IJzxtlinfoService;
import com.ai.service.reviewed.service.ISbhzinfoService;
import com.ai.service.reviewed.service.ISbmxinfoService;
import com.ai.view.BtTotalViewExcel;
import com.ai.view.QysbViewExcel;
import com.ai.view.shreportViewExcel;

/**
 * 补贴审核 
 */
@RequestMapping("btsb")
@Controller("com.ai.controller.reviewed.BtsqController")
public class BtsqController extends BaseController {
	
	@Value("${mainDomain}")
	private String mainDomain;
	
	/**
	 * 跳转
	 * 
	 * @date 2016年6月26日 上午11:29:33
	 * @param name
	 * @return
	 * @throws Exception
	 *             String
	 */
	@RequestMapping("forward/{name}")
	public String forward(@PathVariable String name) throws Exception {
		return "/reviewed/btsq/" + name;
	}
	
	/**
	 * 列表
	 * 
	 * @date 2016年6月26日 上午11:29:07
	 * @param SbhzinfoDTO
	 * @return
	 * @throws Exception
	 *             Pagination<SbhzinfoVO>
	 */
	@ResponseBody
	@RequestMapping("list")
	public Pagination<SbhzinfoVO> list(@ModelAttribute SbhzinfoDTO sbhzinfoDTO)
			throws Exception {
		if (sbhzinfoDTO.getSort()!=null) {
			sbhzinfoDTO.setSort(sbhzinfoDTO.getSort().concat(" " + sbhzinfoDTO.getOrder()));
		}else {
			sbhzinfoDTO.setSort("id desc");
		}
		
		if (sbhzinfoDTO.getSbnf()==null&&sbhzinfoDTO.getSbjd()==null&&sbhzinfoDTO.getHzzt()==null
				&&sbhzinfoDTO.getShreportzt()==null) {
			Pagination<SbhzinfoVO> pagination = new Pagination<SbhzinfoVO>();
			List<SbhzinfoVO> list = new ArrayList<SbhzinfoVO>();
			pagination.setRows(list);
			pagination.setTotal(0);
			return pagination;
		}
		
		return isbhzinfoService.getListVO(sbhzinfoDTO);
	}
	
	/**
	 * 明细列表
	 * 
	 * @date 2016年6月26日 上午11:29:07
	 * @param SbhzinfoDTO
	 * @return
	 * @throws Exception
	 *             Pagination<SbhzinfoVO>
	 */
	@ResponseBody
	@RequestMapping("detaillist")
	public Pagination<SbmxinfoVO> detaillist(@ModelAttribute SbmxinfoDTO sbmxinfoDTO)
			throws Exception {
//		sbmxinfoDTO.setSfsc(0);
//		if (sbmxinfoDTO.getSort()!=null) {
//			sbmxinfoDTO.setSort(sbmxinfoDTO.getSort().concat(" " + sbmxinfoDTO.getOrder()));
//		}else {
//			sbmxinfoDTO.setSort("id");
//		}
		
		//return isbmxinfoService.getListVO(sbmxinfoDTO);
		return isbmxinfoService.getSameDhList(sbmxinfoDTO);
	}
	
	/**
	 * 汇总审核
	 * 
	 * @date 2016年6月26日 下午12:44:40
	 * @param yhxxDTO
	 * @return
	 * @throws Exception
	 *             Result
	 */
	@ResponseBody
	@RequestMapping("audit")
	public Result audit(HttpServletRequest request,
			@ModelAttribute SbhzinfoDTO sbhzinfoDTO) throws Exception {

		AlikAssert.isNotNull(sbhzinfoDTO, "参数异常.");
        AlikAssert.isNotNull(sbhzinfoDTO.getId(), "id参数异常.");
        AlikAssert.isNotNull(sbhzinfoDTO.getHzzt(), "hzzt参数异常.");
        
		Result result = new Result(true);
		
		Integer hzzt = sbhzinfoDTO.getHzzt();
		
		
		
		//查询补贴价格
		SbhzinfoModel sbhzinfoModelbt = isbhzinfoService.getByPrimaryKey(sbhzinfoDTO.getId());
		Map<String, Object> map = new HashMap<String, Object>(); 
		map.put("hzdh", sbhzinfoModelbt.getHzdh());
		map.put("yhid", sbhzinfoModelbt.getYhid());
		map.put("hzzt", 2);
		Map<String, Integer> returnmap = isbhzinfoService.getxhDetail(map);	
		sbhzinfoDTO.setZxl(returnmap.get("xl"));
		sbhzinfoDTO.setBtje(returnmap.get("cash"));
		sbhzinfoDTO.setGxsj(new Date());
		
		//判断明细是否有质疑
		if (hzzt == 3) {
			SbmxinfoDTO sbmxinfoDTO = new SbmxinfoDTO();
			sbmxinfoDTO.setHzdh(sbhzinfoModelbt.getHzdh());
			List<SbmxinfoModel> mxList = isbmxinfoService.getList(sbmxinfoDTO);
			for (SbmxinfoModel sbmxinfoModel : mxList) {
				if(sbmxinfoModel.getIshc()==1)
				{
					result.addAttribute("status", "-1");
					return result;
				}
			}
		}
		
		SbhzinfoModel sbhzinfoModel = new SbhzinfoModel();
		BeanUtils.copyProperties(sbhzinfoDTO, sbhzinfoModel);
		if (hzzt==3) {
			isbhzinfoService.updateMxBatch(sbhzinfoModel, 1);
		}else {
			isbhzinfoService.updateMxBatch(sbhzinfoModel, 2);
		}
		
		
		return result;
	}
	
	/**
	 * 明细通过
	 * 
	 * @date 2016年6月26日 下午12:44:40
	 * @param yhxxDTO
	 * @return
	 * @throws Exception
	 *             Result
	 */
	@ResponseBody
	@RequestMapping("mxaudit")
	public Result mxaudit(HttpServletRequest request,
			@ModelAttribute SbmxinfoDTO sbmxinfoDTO) throws Exception {

		AlikAssert.isNotNull(sbmxinfoDTO, "参数异常.");
        AlikAssert.isNotNull(sbmxinfoDTO.getHzdh(), "参数异常.");
        AlikAssert.isNotNull(sbmxinfoDTO.getTlhph(), "参数异常.");
        AlikAssert.isNotNull(sbmxinfoDTO.getHwydh(), "参数异常.");
        
		Result result = new Result(true);
		
		List<SbmxinfoModel> list = isbmxinfoService.getList(sbmxinfoDTO);
		if (list.size()>0) {
			for (SbmxinfoModel sbmxinfoModel : list) {
				sbmxinfoModel.setIshc(1);
				sbmxinfoModel.setGxsj(new Date());
				isbmxinfoService.updateByPrimaryKey(sbmxinfoModel);
			}
		}
		return result;
	}
	
	/**
	 * 获取单个明细VO
	 * 
	 * @date 2016年6月26日 下午12:44:40
	 * @param yhxxDTO
	 * @return
	 * @throws Exception
	 *             Result
	 */
	@ResponseBody
	@RequestMapping("getOneMxVO")
	public Result getOneMxVO(HttpServletRequest request,
			@ModelAttribute SbmxinfoDTO sbmxinfoDTO) throws Exception {

		AlikAssert.isNotNull(sbmxinfoDTO, "参数异常.");
		AlikAssert.isNotNull(sbmxinfoDTO.getHzdh(), "参数异常.");
        AlikAssert.isNotNull(sbmxinfoDTO.getTlhph(), "参数异常.");
        AlikAssert.isNotNull(sbmxinfoDTO.getHwydh(), "参数异常.");
        
		Result result = new Result(true);
		sbmxinfoDTO.setSfsc(0);
		Pagination<SbmxinfoVO> list = isbmxinfoService.getListVO(sbmxinfoDTO,mainDomain + "/upload/pictures/");
		result.addAttribute("sbmxinfoVO", list.getRows());
		result.rendering();
		
		return result;
	}
	
	/**
	 * 导出企业申报数据Excel
	 * 
	 * @return
	 */
	@RequestMapping("/export/sbExcelView")
	public ModelAndView gkExcelView(@ModelAttribute SbhzinfoDTO sbhzinfoDTO) throws Exception {
		
		sbhzinfoDTO.setHzzt(3);
		List<SbhzinfoVO> list = isbhzinfoService.getListVO(sbhzinfoDTO).getRows();
		
		Map<String, Object> model = new HashMap<String, Object>();
		model.put("list", list);
		model.put("fromyear", sbhzinfoDTO.getFromyear());
		model.put("toyear", sbhzinfoDTO.getToyear());
		model.put("name", getBackendOperator(request).getRealName());
		
		return new ModelAndView(new QysbViewExcel(), model);
	}
	
	/**
	 * 开始比对
	 * 
	 * @date 2016年6月26日 下午12:44:40
	 * @param yhxxDTO
	 * @return
	 * @throws Exception
	 *             Result
	 */
	@ResponseBody
	@RequestMapping(value = "compare",method=RequestMethod.POST)
	public Result compare(HttpServletRequest request,
			@ModelAttribute SbhzinfoDTO sbhzinfoDTO) throws Exception {

		AlikAssert.isNotNull(sbhzinfoDTO, "参数异常.");
        AlikAssert.isNotNull(sbhzinfoDTO.getSbnf(), "参数异常.");
        AlikAssert.isNotNull(sbhzinfoDTO.getSbjd(), "参数异常.");
        
		Result result = new Result(true);
		
		//查询列表
		List<SbmxinfoVO> list = isbmxinfoService.getCompareWorngList(sbhzinfoDTO);
		if (list.size()>0) {
			//更新汇总和明细
			isbhzinfoService.updatehzmx(list);
			//isbhzinfoService.updaterighthzmx(sbhzinfoDTO);
			//result.addAttribute("status", "存在异常数据");
			result.addAttribute("status", -1);
			//result.addAttribute("count", list.size());
			return result;
		}
		
		//判断是否两个时间都存在不是比对年份和季度的情况
		list = isbhzinfoService.getTimeErrorList(sbhzinfoDTO);
		if (list.size()>0) {
			result.addAttribute("status", -2);
			result.addAttribute("list", list);
			return result;
		}
		
		isbhzinfoService.crossAllHz(sbhzinfoDTO);
		result.addAttribute("status", 0);
		return result;
	}
	
	/**
	 * 质疑明细列表
	 * 
	 * @date 2016年6月26日 上午11:29:07
	 * @param SbhzinfoDTO
	 * @return
	 * @throws Exception
	 *             Pagination<SbhzinfoVO>
	 */
	@ResponseBody
	@RequestMapping("zydetaillist")
	public Pagination<SbmxinfoVO> zydetaillist(@ModelAttribute SbhzinfoDTO sbhzinfoDTO)
			throws Exception {
		
		int rowstartindex = (sbhzinfoDTO.getPage()-1)*sbhzinfoDTO.getRows()+1;
		sbhzinfoDTO.setRowstartindex(rowstartindex);
		
		List<SbmxinfoVO> list = isbmxinfoService.getCompareWorngList(sbhzinfoDTO);
		Pagination<SbmxinfoVO> pagination = new Pagination<SbmxinfoVO>();
		pagination.setRows(list);
		pagination.setTotal(isbhzinfoService.getCompareWorngListCount(sbhzinfoDTO));
		
		return pagination;
	}
	
	/**
	 * 明细不通过
	 * 
	 * @date 2016年6月26日 下午12:44:40
	 * @param yhxxDTO
	 * @return
	 * @throws Exception
	 *             Result
	 */
	@ResponseBody
	@RequestMapping("mxzy")
	public Result mxzy(HttpServletRequest request,
			@ModelAttribute SbmxinfoDTO sbmxinfoDTO) throws Exception {

		AlikAssert.isNotNull(sbmxinfoDTO, "参数异常.");
        AlikAssert.isNotNull(sbmxinfoDTO.getHzdh(), "参数异常.");
        AlikAssert.isNotNull(sbmxinfoDTO.getTlhph(), "参数异常.");
        AlikAssert.isNotNull(sbmxinfoDTO.getHwydh(), "参数异常.");
        AlikAssert.isNotNull(sbmxinfoDTO.getJjyy(), "参数异常.");
        
		Result result = new Result(true);
		
		isbmxinfoService.mxzy(sbmxinfoDTO);
		
		return result;
	}
	
	/**
	 * 列表
	 * 
	 * @date 2016年6月26日 上午11:29:07
	 * @param SbhzinfoDTO
	 * @return
	 * @throws Exception
	 *             Pagination<SbhzinfoVO>
	 */
	@ResponseBody
	@RequestMapping("fromtodetail")
	public Pagination<StatisticsVO> fromtodetail(@ModelAttribute StatisticsDTO statisticsDTO)
			throws Exception {
		
		statisticsDTO.setSort("1");
		if (statisticsDTO.getNowyear()==null||"".equals(statisticsDTO.getNowyear())) {
        	Calendar a=Calendar.getInstance();
        	statisticsDTO.setNowyear(String.valueOf(a.get(Calendar.YEAR)));
		}
		List<StatisticsVO> list = isbhzinfoService.getfromtoStatistics(statisticsDTO);
		Pagination<StatisticsVO> pagination = new Pagination<StatisticsVO>();
		pagination.setRows(list);
		pagination.setTotal(10);
		
		return pagination;
	}
	
	/**
	 * 首页总金额、总箱量和总季度统计
	 * 
	 * @date 2016年6月26日 下午12:44:40
	 * @param yhxxDTO
	 * @return
	 * @throws Exception
	 *             Result
	 */
	@ResponseBody
	@RequestMapping("indexthree")
	public Result indexthree(HttpServletRequest request) throws Exception {
        
		Result result = new Result(true);
		
		Map<String, Object> map = new HashMap<String, Object>(); 
		map.put("hzzt", 3);
		Map<String, Integer> returnmap = isbhzinfoService.getxhDetail(map);	

		result.addAttribute("cash", Float.parseFloat(returnmap.get("cash").toString())/10000);
		result.addAttribute("xl", returnmap.get("xl"));
		result.addAttribute("jd", returnmap.get("jd"));
		result.rendering();
		
		return result;
	}
	
	/**
	 * 导出统计表
	 * 
	 * @return
	 */
	@RequestMapping("/export/statisticsTableView")
	public String statisticsTableView(@ModelAttribute SbhzinfoDTO sbhzinfoDTO,HttpServletResponse response) throws Exception {
		
		String sbnf = sbhzinfoDTO.getSbnf().toString();
		
		String excelName = sbnf + "年武汉集装箱铁水联运统计表.xls";
		
		ServletOutputStream outputStream = response.getOutputStream();  
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Content-disposition", "attachment;filename="+ URLEncoder.encode(excelName, "utf-8"));  
		
        HSSFWorkbook workbook = new HSSFWorkbook();
		// 产生Excel表头
		HSSFSheet sheet = workbook.createSheet("分类统计");
		
		//标题样式 start
		HSSFCellStyle titleStyle = workbook.createCellStyle();
		titleStyle.setAlignment(CellStyle.ALIGN_CENTER);
		titleStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

		HSSFFont titlefont = workbook.createFont();
		titlefont.setFontHeightInPoints((short) 24);
		titlefont.setFontName("方正小标宋简体");
		titleStyle.setFont(titlefont);
		//标题样式 end

		//表头 start
		HSSFCellStyle headStyle = workbook.createCellStyle();
		headStyle.setAlignment(CellStyle.ALIGN_CENTER);
		headStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		headStyle.setBorderBottom(CellStyle.BORDER_THIN); //下边框
		headStyle.setBorderLeft(CellStyle.BORDER_THIN);//左边框
		headStyle.setBorderTop(CellStyle.BORDER_THIN);//上边框
		headStyle.setBorderRight(CellStyle.BORDER_THIN);//右边框
		headStyle.setWrapText(true);

		HSSFFont headFont = workbook.createFont();
		headFont.setFontHeightInPoints((short) 12);
		headFont.setFontName("黑体");
		headStyle.setFont(headFont);
		//表头样式 end
		
		HSSFRow  title = sheet.createRow(0);
		title.setHeight((short) 500);
		Cell hsstitle = title.createCell(0);
		hsstitle.setCellValue(sbnf + "年武汉集装箱铁水联运分类情况统计表(单位：TEU)");
		hsstitle.setCellStyle(titleStyle);
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 18));

		Short headHeight = (short) 400;
		HSSFRow  firtsthead = sheet.createRow(1);
		HSSFRow  secondthead = sheet.createRow(2);
		firtsthead.setHeight(headHeight);
		secondthead.setHeight(headHeight);
		firtsthead.setHeightInPoints(29);
		secondthead.setHeightInPoints(29);
		
		//表头第一行
		HSSFCell h_sqdw = firtsthead.createCell(0);
		h_sqdw.setCellValue("申请单位");
		h_sqdw.setCellStyle(headStyle);
		HSSFCell h_sqdw2 = secondthead.createCell(0);
		h_sqdw2.setCellStyle(headStyle);
//		sheet.addMergedRegion(new CellRangeAddress(1, 2, 0, 0));
		
		HSSFCell h_jd = firtsthead.createCell(1);
		h_jd.setCellValue("季度");
		h_jd.setCellStyle(headStyle);
		HSSFCell h_jd2 = secondthead.createCell(1);
		h_jd2.setCellStyle(headStyle);
//		sheet.addMergedRegion(new CellRangeAddress(1, 2, 1, 1));
		
		HSSFCell h_zyfs = firtsthead.createCell(2);
		h_zyfs.setCellValue("转运方式");
		h_zyfs.setCellStyle(headStyle);
		HSSFCell h_zyfs3 = firtsthead.createCell(3);
		h_zyfs3.setCellStyle(headStyle);
		HSSFCell h_zyfs4 = firtsthead.createCell(4);
		h_zyfs4.setCellStyle(headStyle);
		HSSFCell h_zyfs5 = firtsthead.createCell(5);
		h_zyfs5.setCellStyle(headStyle);
//		sheet.addMergedRegion(new CellRangeAddress(1, 1, 2, 5));

		HSSFCell h_zycz = firtsthead.createCell(6);
		h_zycz.setCellValue("转运车站");
		h_zycz.setCellStyle(headStyle);
		HSSFCell h_zycz7 = firtsthead.createCell(7);
		h_zycz7.setCellStyle(headStyle);
		HSSFCell h_zycz8 = firtsthead.createCell(8);
		h_zycz8.setCellStyle(headStyle);
		HSSFCell h_zycz9 = firtsthead.createCell(9);
		h_zycz9.setCellStyle(headStyle);
//		sheet.addMergedRegion(new CellRangeAddress(1, 1, 6, 9));
		
		HSSFCell h_zymt = firtsthead.createCell(10);
		h_zymt.setCellValue("转运码头");
		h_zymt.setCellStyle(headStyle);
		HSSFCell h_zymt11 = firtsthead.createCell(11);
		h_zymt11.setCellStyle(headStyle);
		HSSFCell h_zymt12 = firtsthead.createCell(12);
		h_zymt12.setCellStyle(headStyle);
		HSSFCell h_zymt13 = firtsthead.createCell(13);
		h_zymt13.setCellStyle(headStyle);
//		sheet.addMergedRegion(new CellRangeAddress(1, 1, 10, 13));

		HSSFCell h_mylx = firtsthead.createCell(14);
		h_mylx.setCellValue("内/外贸");
		h_mylx.setCellStyle(headStyle);
		HSSFCell h_mylx15 = firtsthead.createCell(15);
		h_mylx15.setCellStyle(headStyle);
		HSSFCell h_mylx16 = firtsthead.createCell(16);
		h_mylx16.setCellStyle(headStyle);
		HSSFCell h_mylx17 = firtsthead.createCell(17);
		h_mylx17.setCellStyle(headStyle);
//		sheet.addMergedRegion(new CellRangeAddress(1, 1, 14, 17));

		HSSFCell h_qnhj = firtsthead.createCell(18);
		h_qnhj.setCellValue("合计");
		h_qnhj.setCellStyle(headStyle);
		HSSFCell h_qnhj2 = secondthead.createCell(18);
		h_qnhj2.setCellStyle(headStyle);
//		sheet.addMergedRegion(new CellRangeAddress(1, 2, 18, 18));
		
		//表头第二行
		HSSFCell h_ts = secondthead.createCell(2);
		h_ts.setCellValue("铁路-水路");
		h_ts.setCellStyle(headStyle);
		
		HSSFCell h_tszb = secondthead.createCell(3);
		h_tszb.setCellValue("占比");
		h_tszb.setCellStyle(headStyle);
		
		HSSFCell h_st = secondthead.createCell(4);
		h_st.setCellValue("水路-铁路");
		h_st.setCellStyle(headStyle);
		
		HSSFCell h_stzb = secondthead.createCell(5);
		h_stzb.setCellValue("占比");
		h_stzb.setCellStyle(headStyle);
				
		HSSFCell h_skz = secondthead.createCell(6);
		h_skz.setCellValue("滠口站");
		h_skz.setCellStyle(headStyle);
		
		HSSFCell h_skzb = secondthead.createCell(7);
		h_skzb.setCellValue("占比");
		h_skzb.setCellStyle(headStyle);
		
		HSSFCell h_wjsz = secondthead.createCell(8);
		h_wjsz.setCellValue("吴家山中心站");
		h_wjsz.setCellStyle(headStyle);
		
		HSSFCell h_wjszb = secondthead.createCell(9);
		h_wjszb.setCellValue("占比");
		h_wjszb.setCellStyle(headStyle);
		
		HSSFCell h_yq = secondthead.createCell(10);
		h_yq.setCellValue("一期(WIT)");
		h_yq.setCellStyle(headStyle);
		
		HSSFCell h_yqzb = secondthead.createCell(11);
		h_yqzb.setCellValue("占比");
		h_yqzb.setCellStyle(headStyle);
		
		HSSFCell h_eq = secondthead.createCell(12);
		h_eq.setCellValue("二期(武港集箱)");
		h_eq.setCellStyle(headStyle);
		
		HSSFCell h_eqzb = secondthead.createCell(13);
		h_eqzb.setCellValue("占比");
		h_eqzb.setCellStyle(headStyle);
		
		HSSFCell h_nmx = secondthead.createCell(14);
		h_nmx.setCellValue("内贸箱");
		h_nmx.setCellStyle(headStyle);
		
		HSSFCell h_nmxzb = secondthead.createCell(15);
		h_nmxzb.setCellValue("占比");
		h_nmxzb.setCellStyle(headStyle);
		
		HSSFCell h_wmx = secondthead.createCell(16);
		h_wmx.setCellValue("外贸箱");
		h_wmx.setCellStyle(headStyle);
		
		HSSFCell h_wmxzb = secondthead.createCell(17);
		h_wmxzb.setCellValue("占比");
		h_wmxzb.setCellStyle(headStyle);
		
		sheet.addMergedRegion(new CellRangeAddress(1, 2, 0, 0));
		sheet.addMergedRegion(new CellRangeAddress(1, 2, 1, 1));
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 2, 5));
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 6, 9));
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 10, 13));
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 14, 17));
		sheet.addMergedRegion(new CellRangeAddress(1, 2, 18, 18));
		
		//编制企业名录
		sbhzinfoDTO.setHzzt(3);
		List<YhxxVO> yhxxList = isbhzinfoService.getCompanyList(sbhzinfoDTO);
		Integer i = 3;
		for (YhxxVO yhxxVO : yhxxList) {
			HSSFRow  firstrow = sheet.createRow(i);
			HSSFRow  secondrow = sheet.createRow(i+1);
			HSSFRow  thirthrow = sheet.createRow(i+2);
			HSSFRow  forthrow = sheet.createRow(i+3);
			HSSFRow  fifthrow = sheet.createRow(i+4);
			
			firstrow.createCell(0).setCellValue(yhxxVO.getQyqc());
			firstrow.createCell(1).setCellValue("Ⅰ");
			secondrow.createCell(1).setCellValue("Ⅱ");
			thirthrow.createCell(1).setCellValue("Ⅲ");
			forthrow.createCell(1).setCellValue("Ⅳ");
			fifthrow.createCell(1).setCellValue("小计");
			
			if (firstrow.getCell(0)!=null) {
				firstrow.getCell(0).setCellStyle(headStyle);
			}else {
				firstrow.createCell(0).setCellStyle(headStyle);
			}
			if (secondrow.getCell(0)!=null) {
				secondrow.getCell(0).setCellStyle(headStyle);
			}else {
				secondrow.createCell(0).setCellStyle(headStyle);
			}
			if (thirthrow.getCell(0)!=null) {
				thirthrow.getCell(0).setCellStyle(headStyle);
			}else {
				thirthrow.createCell(0).setCellStyle(headStyle);
			}
			if (forthrow.getCell(0)!=null) {
				forthrow.getCell(0).setCellStyle(headStyle);
			}else {
				forthrow.createCell(0).setCellStyle(headStyle);
			}
			if (fifthrow.getCell(0)!=null) {
				fifthrow.getCell(0).setCellStyle(headStyle);
			}else {
				fifthrow.createCell(0).setCellStyle(headStyle);
			}
			sheet.addMergedRegion(new CellRangeAddress(i, i+4, 0, 0));
			
			i = i + 5;
		}
		
		//编制总量
		HSSFRow  lastrow = sheet.createRow(i);
		lastrow.createCell(0).setCellValue("总量");
		lastrow.getCell(0).setCellStyle(headStyle);
		
		//开始填充数据
		sbhzinfoDTO.setHzzt(3);
		sbhzinfoDTO.setSort("a.yhid");
		StatisticsVO statisticsVO = null;
		HSSFRow row = null;
		Integer j = 3;
		
		//第一季度
		sbhzinfoDTO.setSbjd(1);
		for (YhxxVO yhxxVO : yhxxList) {
			//1.铁水
			sbhzinfoDTO.setTjflag(1);
			sbhzinfoDTO.setYhid(yhxxVO.getYhid());
			statisticsVO = isbhzinfoService.getALLStatistics(sbhzinfoDTO);
			if (statisticsVO!=null) {
				row = sheet.getRow(j);
				if (row.getCell(2)!=null) {
					row.getCell(2).setCellValue(statisticsVO.getCount());
				}else {
					row.createCell(2).setCellValue(statisticsVO.getCount());
				}
			}else {
				row = sheet.getRow(j);
				if (row.getCell(2)!=null) {
					row.getCell(2).setCellValue(0);
				}else {
					row.createCell(2).setCellValue(0);
				}
			}
			
			//2.水铁
			sbhzinfoDTO.setTjflag(2);
			sbhzinfoDTO.setYhid(yhxxVO.getYhid());
			statisticsVO = isbhzinfoService.getALLStatistics(sbhzinfoDTO);
			if (statisticsVO!=null) {
				row = sheet.getRow(j);
				if (row.getCell(4)!=null) {
					row.getCell(4).setCellValue(statisticsVO.getCount());
				}else {
					row.createCell(4).setCellValue(statisticsVO.getCount());
				}
			}else {
				row = sheet.getRow(j);
				if (row.getCell(4)!=null) {
					row.getCell(4).setCellValue(0);
				}else {
					row.createCell(4).setCellValue(0);
				}
			}
			
			//3.滠口站
			sbhzinfoDTO.setTjflag(3);
			sbhzinfoDTO.setYhid(yhxxVO.getYhid());
			statisticsVO = isbhzinfoService.getALLStatistics(sbhzinfoDTO);
			if (statisticsVO!=null) {
				row = sheet.getRow(j);
				if (row.getCell(6)!=null) {
					row.getCell(6).setCellValue(statisticsVO.getCount());
				}else {
					row.createCell(6).setCellValue(statisticsVO.getCount());
				}
			}else {
				row = sheet.getRow(j);
				if (row.getCell(6)!=null) {
					row.getCell(6).setCellValue(0);
				}else {
					row.createCell(6).setCellValue(0);
				}
			}
			
			//4.吴家山站
			sbhzinfoDTO.setTjflag(4);
			sbhzinfoDTO.setYhid(yhxxVO.getYhid());
			statisticsVO = isbhzinfoService.getALLStatistics(sbhzinfoDTO);
			if (statisticsVO!=null) {
				row = sheet.getRow(j);
				if (row.getCell(8)!=null) {
					row.getCell(8).setCellValue(statisticsVO.getCount());
				}else {
					row.createCell(8).setCellValue(statisticsVO.getCount());
				}
			}else {
				row = sheet.getRow(j);
				if (row.getCell(8)!=null) {
					row.getCell(8).setCellValue(0);
				}else {
					row.createCell(8).setCellValue(0);
				}
			}
			
			//5.一期WIT
			sbhzinfoDTO.setTjflag(5);
			sbhzinfoDTO.setYhid(yhxxVO.getYhid());
			statisticsVO = isbhzinfoService.getALLStatistics(sbhzinfoDTO);
			if (statisticsVO!=null) {
				row = sheet.getRow(j);
				if (row.getCell(10)!=null) {
					row.getCell(10).setCellValue(statisticsVO.getCount());
				}else {
					row.createCell(10).setCellValue(statisticsVO.getCount());
				}
			}else {
				row = sheet.getRow(j);
				if (row.getCell(10)!=null) {
					row.getCell(10).setCellValue(0);
				}else {
					row.createCell(10).setCellValue(0);
				}
			}
			
			//6.二期武港集箱
			sbhzinfoDTO.setTjflag(6);
			sbhzinfoDTO.setYhid(yhxxVO.getYhid());
			statisticsVO = isbhzinfoService.getALLStatistics(sbhzinfoDTO);
			if (statisticsVO!=null) {
				row = sheet.getRow(j);
				if (row.getCell(12)!=null) {
					row.getCell(12).setCellValue(statisticsVO.getCount());
				}else {
					row.createCell(12).setCellValue(statisticsVO.getCount());
				}
			}else {
				row = sheet.getRow(j);
				if (row.getCell(12)!=null) {
					row.getCell(12).setCellValue(0);
				}else {
					row.createCell(12).setCellValue(0);
				}
			}
			
			//7.内贸箱
			sbhzinfoDTO.setTjflag(7);
			sbhzinfoDTO.setYhid(yhxxVO.getYhid());
			statisticsVO = isbhzinfoService.getALLStatistics(sbhzinfoDTO);
			if (statisticsVO!=null) {
				row = sheet.getRow(j);
				if (row.getCell(14)!=null) {
					row.getCell(14).setCellValue(statisticsVO.getCount());
				}else {
					row.createCell(14).setCellValue(statisticsVO.getCount());
				}
			}else {
				row = sheet.getRow(j);
				if (row.getCell(14)!=null) {
					row.getCell(14).setCellValue(0);
				}else {
					row.createCell(14).setCellValue(0);
				}
			}
			
			//8.外贸箱
			sbhzinfoDTO.setTjflag(8);
			sbhzinfoDTO.setYhid(yhxxVO.getYhid());
			statisticsVO = isbhzinfoService.getALLStatistics(sbhzinfoDTO);
			if (statisticsVO!=null) {
				row = sheet.getRow(j);
				if (row.getCell(16)!=null) {
					row.getCell(16).setCellValue(statisticsVO.getCount());
				}else {
					row.createCell(16).setCellValue(statisticsVO.getCount());
				}
			}else {
				row = sheet.getRow(j);
				if (row.getCell(16)!=null) {
					row.getCell(16).setCellValue(0);
				}else {
					row.createCell(16).setCellValue(0);
				}
			}
			
			//9.全年合计
//			row.getCell(18).setCellType(HSSFCell.CELL_TYPE_FORMULA);
//			row.getCell(18).setCellFormula("C4+E4");
			
			j = j + 5;
		}
		
		//第二季度
		j = 4;
		sbhzinfoDTO.setSbjd(2);
		for (YhxxVO yhxxVO : yhxxList) {
			//1.铁水
			sbhzinfoDTO.setTjflag(1);
			sbhzinfoDTO.setYhid(yhxxVO.getYhid());
			statisticsVO = isbhzinfoService.getALLStatistics(sbhzinfoDTO);
			if (statisticsVO!=null) {
				row = sheet.getRow(j);
				if (row.getCell(2)!=null) {
					row.getCell(2).setCellValue(statisticsVO.getCount());
				}else {
					row.createCell(2).setCellValue(statisticsVO.getCount());
				}
			}else {
				row = sheet.getRow(j);
				if (row.getCell(2)!=null) {
					row.getCell(2).setCellValue(0);
				}else {
					row.createCell(2).setCellValue(0);
				}
			}
			
			//2.水铁
			sbhzinfoDTO.setTjflag(2);
			sbhzinfoDTO.setYhid(yhxxVO.getYhid());
			statisticsVO = isbhzinfoService.getALLStatistics(sbhzinfoDTO);
			if (statisticsVO!=null) {
				row = sheet.getRow(j);
				if (row.getCell(4)!=null) {
					row.getCell(4).setCellValue(statisticsVO.getCount());
				}else {
					row.createCell(4).setCellValue(statisticsVO.getCount());
				}
			}else {
				row = sheet.getRow(j);
				if (row.getCell(4)!=null) {
					row.getCell(4).setCellValue(0);
				}else {
					row.createCell(4).setCellValue(0);
				}
			}
			
			//3.滠口站
			sbhzinfoDTO.setTjflag(3);
			sbhzinfoDTO.setYhid(yhxxVO.getYhid());
			statisticsVO = isbhzinfoService.getALLStatistics(sbhzinfoDTO);
			if (statisticsVO!=null) {
				row = sheet.getRow(j);
				if (row.getCell(6)!=null) {
					row.getCell(6).setCellValue(statisticsVO.getCount());
				}else {
					row.createCell(6).setCellValue(statisticsVO.getCount());
				}
			}else {
				row = sheet.getRow(j);
				if (row.getCell(6)!=null) {
					row.getCell(6).setCellValue(0);
				}else {
					row.createCell(6).setCellValue(0);
				}
			}
			
			//4.吴家山站
			sbhzinfoDTO.setTjflag(4);
			sbhzinfoDTO.setYhid(yhxxVO.getYhid());
			statisticsVO = isbhzinfoService.getALLStatistics(sbhzinfoDTO);
			if (statisticsVO!=null) {
				row = sheet.getRow(j);
				if (row.getCell(8)!=null) {
					row.getCell(8).setCellValue(statisticsVO.getCount());
				}else {
					row.createCell(8).setCellValue(statisticsVO.getCount());
				}
			}else {
				row = sheet.getRow(j);
				if (row.getCell(8)!=null) {
					row.getCell(8).setCellValue(0);
				}else {
					row.createCell(8).setCellValue(0);
				}
			}
			
			//5.一期WIT
			sbhzinfoDTO.setTjflag(5);
			sbhzinfoDTO.setYhid(yhxxVO.getYhid());
			statisticsVO = isbhzinfoService.getALLStatistics(sbhzinfoDTO);
			if (statisticsVO!=null) {
				row = sheet.getRow(j);
				if (row.getCell(10)!=null) {
					row.getCell(10).setCellValue(statisticsVO.getCount());
				}else {
					row.createCell(10).setCellValue(statisticsVO.getCount());
				}
			}else {
				row = sheet.getRow(j);
				if (row.getCell(10)!=null) {
					row.getCell(10).setCellValue(0);
				}else {
					row.createCell(10).setCellValue(0);
				}
			}
			
			//6.二期武港集箱
			sbhzinfoDTO.setTjflag(6);
			sbhzinfoDTO.setYhid(yhxxVO.getYhid());
			statisticsVO = isbhzinfoService.getALLStatistics(sbhzinfoDTO);
			if (statisticsVO!=null) {
				row = sheet.getRow(j);
				if (row.getCell(12)!=null) {
					row.getCell(12).setCellValue(statisticsVO.getCount());
				}else {
					row.createCell(12).setCellValue(statisticsVO.getCount());
				}
			}else {
				row = sheet.getRow(j);
				if (row.getCell(12)!=null) {
					row.getCell(12).setCellValue(0);
				}else {
					row.createCell(12).setCellValue(0);
				}
			}
			
			//7.内贸箱
			sbhzinfoDTO.setTjflag(7);
			sbhzinfoDTO.setYhid(yhxxVO.getYhid());
			statisticsVO = isbhzinfoService.getALLStatistics(sbhzinfoDTO);
			if (statisticsVO!=null) {
				row = sheet.getRow(j);
				if (row.getCell(14)!=null) {
					row.getCell(14).setCellValue(statisticsVO.getCount());
				}else {
					row.createCell(14).setCellValue(statisticsVO.getCount());
				}
			}else {
				row = sheet.getRow(j);
				if (row.getCell(14)!=null) {
					row.getCell(14).setCellValue(0);
				}else {
					row.createCell(14).setCellValue(0);
				}
			}
			
			//8.外贸箱
			sbhzinfoDTO.setTjflag(8);
			sbhzinfoDTO.setYhid(yhxxVO.getYhid());
			statisticsVO = isbhzinfoService.getALLStatistics(sbhzinfoDTO);
			if (statisticsVO!=null) {
				row = sheet.getRow(j);
				if (row.getCell(16)!=null) {
					row.getCell(16).setCellValue(statisticsVO.getCount());
				}else {
					row.createCell(16).setCellValue(statisticsVO.getCount());
				}
			}else {
				row = sheet.getRow(j);
				if (row.getCell(16)!=null) {
					row.getCell(16).setCellValue(0);
				}else {
					row.createCell(16).setCellValue(0);
				}
			}
			j = j + 5;
		}
		
		//第三季度
		j = 5;
		sbhzinfoDTO.setSbjd(3);
		for (YhxxVO yhxxVO : yhxxList) {
			//1.铁水
			sbhzinfoDTO.setTjflag(1);
			sbhzinfoDTO.setYhid(yhxxVO.getYhid());
			statisticsVO = isbhzinfoService.getALLStatistics(sbhzinfoDTO);
			if (statisticsVO!=null) {
				row = sheet.getRow(j);
				if (row.getCell(2)!=null) {
					row.getCell(2).setCellValue(statisticsVO.getCount());
				}else {
					row.createCell(2).setCellValue(statisticsVO.getCount());
				}
			}else {
				row = sheet.getRow(j);
				if (row.getCell(2)!=null) {
					row.getCell(2).setCellValue(0);
				}else {
					row.createCell(2).setCellValue(0);
				}
			}
			
			//2.水铁
			sbhzinfoDTO.setTjflag(2);
			sbhzinfoDTO.setYhid(yhxxVO.getYhid());
			statisticsVO = isbhzinfoService.getALLStatistics(sbhzinfoDTO);
			if (statisticsVO!=null) {
				row = sheet.getRow(j);
				if (row.getCell(4)!=null) {
					row.getCell(4).setCellValue(statisticsVO.getCount());
				}else {
					row.createCell(4).setCellValue(statisticsVO.getCount());
				}
			}else {
				row = sheet.getRow(j);
				if (row.getCell(4)!=null) {
					row.getCell(4).setCellValue(0);
				}else {
					row.createCell(4).setCellValue(0);
				}
			}
			
			//3.滠口站
			sbhzinfoDTO.setTjflag(3);
			sbhzinfoDTO.setYhid(yhxxVO.getYhid());
			statisticsVO = isbhzinfoService.getALLStatistics(sbhzinfoDTO);
			if (statisticsVO!=null) {
				row = sheet.getRow(j);
				if (row.getCell(6)!=null) {
					row.getCell(6).setCellValue(statisticsVO.getCount());
				}else {
					row.createCell(6).setCellValue(statisticsVO.getCount());
				}
			}else {
				row = sheet.getRow(j);
				if (row.getCell(6)!=null) {
					row.getCell(6).setCellValue(0);
				}else {
					row.createCell(6).setCellValue(0);
				}
			}
			
			//4.吴家山站
			sbhzinfoDTO.setTjflag(4);
			sbhzinfoDTO.setYhid(yhxxVO.getYhid());
			statisticsVO = isbhzinfoService.getALLStatistics(sbhzinfoDTO);
			if (statisticsVO!=null) {
				row = sheet.getRow(j);
				if (row.getCell(8)!=null) {
					row.getCell(8).setCellValue(statisticsVO.getCount());
				}else {
					row.createCell(8).setCellValue(statisticsVO.getCount());
				}
			}else {
				row = sheet.getRow(j);
				if (row.getCell(8)!=null) {
					row.getCell(8).setCellValue(0);
				}else {
					row.createCell(8).setCellValue(0);
				}
			}
			
			//5.一期WIT
			sbhzinfoDTO.setTjflag(5);
			sbhzinfoDTO.setYhid(yhxxVO.getYhid());
			statisticsVO = isbhzinfoService.getALLStatistics(sbhzinfoDTO);
			if (statisticsVO!=null) {
				row = sheet.getRow(j);
				if (row.getCell(10)!=null) {
					row.getCell(10).setCellValue(statisticsVO.getCount());
				}else {
					row.createCell(10).setCellValue(statisticsVO.getCount());
				}
			}else {
				row = sheet.getRow(j);
				if (row.getCell(10)!=null) {
					row.getCell(10).setCellValue(0);
				}else {
					row.createCell(10).setCellValue(0);
				}
			}
			
			//6.二期武港集箱
			sbhzinfoDTO.setTjflag(6);
			sbhzinfoDTO.setYhid(yhxxVO.getYhid());
			statisticsVO = isbhzinfoService.getALLStatistics(sbhzinfoDTO);
			if (statisticsVO!=null) {
				row = sheet.getRow(j);
				if (row.getCell(12)!=null) {
					row.getCell(12).setCellValue(statisticsVO.getCount());
				}else {
					row.createCell(12).setCellValue(statisticsVO.getCount());
				}
			}else {
				row = sheet.getRow(j);
				if (row.getCell(12)!=null) {
					row.getCell(12).setCellValue(0);
				}else {
					row.createCell(12).setCellValue(0);
				}
			}
			
			//7.内贸箱
			sbhzinfoDTO.setTjflag(7);
			sbhzinfoDTO.setYhid(yhxxVO.getYhid());
			statisticsVO = isbhzinfoService.getALLStatistics(sbhzinfoDTO);
			if (statisticsVO!=null) {
				row = sheet.getRow(j);
				if (row.getCell(14)!=null) {
					row.getCell(14).setCellValue(statisticsVO.getCount());
				}else {
					row.createCell(14).setCellValue(statisticsVO.getCount());
				}
			}else {
				row = sheet.getRow(j);
				if (row.getCell(14)!=null) {
					row.getCell(14).setCellValue(0);
				}else {
					row.createCell(14).setCellValue(0);
				}
			}
			
			//8.外贸箱
			sbhzinfoDTO.setTjflag(8);
			sbhzinfoDTO.setYhid(yhxxVO.getYhid());
			statisticsVO = isbhzinfoService.getALLStatistics(sbhzinfoDTO);
			if (statisticsVO!=null) {
				row = sheet.getRow(j);
				if (row.getCell(16)!=null) {
					row.getCell(16).setCellValue(statisticsVO.getCount());
				}else {
					row.createCell(16).setCellValue(statisticsVO.getCount());
				}
			}else {
				row = sheet.getRow(j);
				if (row.getCell(16)!=null) {
					row.getCell(16).setCellValue(0);
				}else {
					row.createCell(16).setCellValue(0);
				}
			}
			j = j + 5;
		}
		
		//第四季度
		j = 6;
		sbhzinfoDTO.setSbjd(4);
		for (YhxxVO yhxxVO : yhxxList) {
			//1.铁水
			sbhzinfoDTO.setTjflag(1);
			sbhzinfoDTO.setYhid(yhxxVO.getYhid());
			statisticsVO = isbhzinfoService.getALLStatistics(sbhzinfoDTO);
			if (statisticsVO!=null) {
				row = sheet.getRow(j);
				if (row.getCell(2)!=null) {
					row.getCell(2).setCellValue(statisticsVO.getCount());
				}else {
					row.createCell(2).setCellValue(statisticsVO.getCount());
				}
			}else {
				row = sheet.getRow(j);
				if (row.getCell(2)!=null) {
					row.getCell(2).setCellValue(0);
				}else {
					row.createCell(2).setCellValue(0);
				}
			}
			
			//2.水铁
			sbhzinfoDTO.setTjflag(2);
			sbhzinfoDTO.setYhid(yhxxVO.getYhid());
			statisticsVO = isbhzinfoService.getALLStatistics(sbhzinfoDTO);
			if (statisticsVO!=null) {
				row = sheet.getRow(j);
				if (row.getCell(4)!=null) {
					row.getCell(4).setCellValue(statisticsVO.getCount());
				}else {
					row.createCell(4).setCellValue(statisticsVO.getCount());
				}
			}else {
				row = sheet.getRow(j);
				if (row.getCell(4)!=null) {
					row.getCell(4).setCellValue(0);
				}else {
					row.createCell(4).setCellValue(0);
				}
			}
			
			//3.滠口站
			sbhzinfoDTO.setTjflag(3);
			sbhzinfoDTO.setYhid(yhxxVO.getYhid());
			statisticsVO = isbhzinfoService.getALLStatistics(sbhzinfoDTO);
			if (statisticsVO!=null) {
				row = sheet.getRow(j);
				if (row.getCell(6)!=null) {
					row.getCell(6).setCellValue(statisticsVO.getCount());
				}else {
					row.createCell(6).setCellValue(statisticsVO.getCount());
				}
			}else {
				row = sheet.getRow(j);
				if (row.getCell(6)!=null) {
					row.getCell(6).setCellValue(0);
				}else {
					row.createCell(6).setCellValue(0);
				}
			}
			
			//4.吴家山站
			sbhzinfoDTO.setTjflag(4);
			sbhzinfoDTO.setYhid(yhxxVO.getYhid());
			statisticsVO = isbhzinfoService.getALLStatistics(sbhzinfoDTO);
			if (statisticsVO!=null) {
				row = sheet.getRow(j);
				if (row.getCell(8)!=null) {
					row.getCell(8).setCellValue(statisticsVO.getCount());
				}else {
					row.createCell(8).setCellValue(statisticsVO.getCount());
				}
			}else {
				row = sheet.getRow(j);
				if (row.getCell(8)!=null) {
					row.getCell(8).setCellValue(0);
				}else {
					row.createCell(8).setCellValue(0);
				}
			}
			
			//5.一期WIT
			sbhzinfoDTO.setTjflag(5);
			sbhzinfoDTO.setYhid(yhxxVO.getYhid());
			statisticsVO = isbhzinfoService.getALLStatistics(sbhzinfoDTO);
			if (statisticsVO!=null) {
				row = sheet.getRow(j);
				if (row.getCell(10)!=null) {
					row.getCell(10).setCellValue(statisticsVO.getCount());
				}else {
					row.createCell(10).setCellValue(statisticsVO.getCount());
				}
			}else {
				row = sheet.getRow(j);
				if (row.getCell(10)!=null) {
					row.getCell(10).setCellValue(0);
				}else {
					row.createCell(10).setCellValue(0);
				}
			}
			
			//6.二期武港集箱
			sbhzinfoDTO.setTjflag(6);
			sbhzinfoDTO.setYhid(yhxxVO.getYhid());
			statisticsVO = isbhzinfoService.getALLStatistics(sbhzinfoDTO);
			if (statisticsVO!=null) {
				row = sheet.getRow(j);
				if (row.getCell(12)!=null) {
					row.getCell(12).setCellValue(statisticsVO.getCount());
				}else {
					row.createCell(12).setCellValue(statisticsVO.getCount());
				}
			}else {
				row = sheet.getRow(j);
				if (row.getCell(12)!=null) {
					row.getCell(12).setCellValue(0);
				}else {
					row.createCell(12).setCellValue(0);
				}
			}
			
			//7.内贸箱
			sbhzinfoDTO.setTjflag(7);
			sbhzinfoDTO.setYhid(yhxxVO.getYhid());
			statisticsVO = isbhzinfoService.getALLStatistics(sbhzinfoDTO);
			if (statisticsVO!=null) {
				row = sheet.getRow(j);
				if (row.getCell(14)!=null) {
					row.getCell(14).setCellValue(statisticsVO.getCount());
				}else {
					row.createCell(14).setCellValue(statisticsVO.getCount());
				}
			}else {
				row = sheet.getRow(j);
				if (row.getCell(14)!=null) {
					row.getCell(14).setCellValue(0);
				}else {
					row.createCell(14).setCellValue(0);
				}
			}
			
			//8.外贸箱
			sbhzinfoDTO.setTjflag(8);
			sbhzinfoDTO.setYhid(yhxxVO.getYhid());
			statisticsVO = isbhzinfoService.getALLStatistics(sbhzinfoDTO);
			if (statisticsVO!=null) {
				row = sheet.getRow(j);
				if (row.getCell(16)!=null) {
					row.getCell(16).setCellValue(statisticsVO.getCount());
				}else {
					row.createCell(16).setCellValue(statisticsVO.getCount());
				}
			}else {
				row = sheet.getRow(j);
				if (row.getCell(16)!=null) {
					row.getCell(16).setCellValue(0);
				}else {
					row.createCell(16).setCellValue(0);
				}
			}
			j = j + 5;
		}
		
		//全年合计
		int z = 3;
		for (int t=0;t<yhxxList.size();t++) {
			row = sheet.getRow(z);
			if (row.getCell(18)!=null) {
				row.getCell(18).setCellType(HSSFCell.CELL_TYPE_FORMULA);
				row.getCell(18).setCellFormula("C"+ (z+1) +"+E"+ (z+1));
			}else {
				row.createCell(18).setCellType(HSSFCell.CELL_TYPE_FORMULA);
				row.createCell(18).setCellFormula("C"+ (z+1) +"+E"+ (z+1));
			}

			row = sheet.getRow(z+1);
			if (row.getCell(18)!=null) {
				row.getCell(18).setCellType(HSSFCell.CELL_TYPE_FORMULA);
				row.getCell(18).setCellFormula("C"+ (z+2) +"+E"+ (z+12));
			}else {
				row.createCell(18).setCellType(HSSFCell.CELL_TYPE_FORMULA);
				row.createCell(18).setCellFormula("C"+ (z+2) +"+E"+ (z+2));
			}
			
			row = sheet.getRow(z+2);
			if (row.getCell(18)!=null) {
				row.getCell(18).setCellType(HSSFCell.CELL_TYPE_FORMULA);
				row.getCell(18).setCellFormula("C"+ (z+3) +"+E"+ (z+3));
			}else {
				row.createCell(18).setCellType(HSSFCell.CELL_TYPE_FORMULA);
				row.createCell(18).setCellFormula("C"+ (z+3) +"+E"+ (z+3));
			}
			
			row = sheet.getRow(z+3);
			if (row.getCell(18)!=null) {
				row.getCell(18).setCellType(HSSFCell.CELL_TYPE_FORMULA);
				row.getCell(18).setCellFormula("C"+ (z+4) +"+E"+ (z+4));
			}else {
				row.createCell(18).setCellType(HSSFCell.CELL_TYPE_FORMULA);
				row.createCell(18).setCellFormula("C"+ (z+4) +"+E"+ (z+4));
			}
			
			z = z + 5;
		}
		
		//小计
		z = 7;
		for (int t=0;t<yhxxList.size();t++) {
			row = sheet.getRow(z);
			//铁水
			if (row.getCell(2)!=null) {
				row.getCell(2).setCellType(HSSFCell.CELL_TYPE_FORMULA);
				row.getCell(2).setCellFormula("C"+ (z-3) + "+C"+ (z-2) + "+C"+ (z-1) + "+C"+ (z));
			}else {
				row.createCell(2).setCellType(HSSFCell.CELL_TYPE_FORMULA);
				row.getCell(2).setCellFormula("C"+ (z-3) + "+C"+ (z-2) + "+C"+ (z-1) + "+C"+ (z));
			}
			
			//水铁
			if (row.getCell(4)!=null) {
				row.getCell(4).setCellType(HSSFCell.CELL_TYPE_FORMULA);
				row.getCell(4).setCellFormula("E"+ (z-3) + "+E"+ (z-2) + "+E"+ (z-1) + "+E"+ (z));
			}else {
				row.createCell(4).setCellType(HSSFCell.CELL_TYPE_FORMULA);
				row.getCell(4).setCellFormula("E"+ (z-3) + "+E"+ (z-2) + "+E"+ (z-1) + "+E"+ (z));
			}
			
			//滠口站
			if (row.getCell(6)!=null) {
				row.getCell(6).setCellType(HSSFCell.CELL_TYPE_FORMULA);
				row.getCell(6).setCellFormula("G"+ (z-3) + "+G"+ (z-2) + "+G"+ (z-1) + "+G"+ (z));
			}else {
				row.createCell(6).setCellType(HSSFCell.CELL_TYPE_FORMULA);
				row.getCell(6).setCellFormula("G"+ (z-3) + "+G"+ (z-2) + "+G"+ (z-1) + "+G"+ (z));
			}
			
			//吴家山站
			if (row.getCell(8)!=null) {
				row.getCell(8).setCellType(HSSFCell.CELL_TYPE_FORMULA);
				row.getCell(8).setCellFormula("I"+ (z-3) + "+I"+ (z-2) + "+I"+ (z-1) + "+I"+ (z));
			}else {
				row.createCell(8).setCellType(HSSFCell.CELL_TYPE_FORMULA);
				row.getCell(8).setCellFormula("I"+ (z-3) + "+I"+ (z-2) + "+I"+ (z-1) + "+I"+ (z));
			}
			
			//一期
			if (row.getCell(10)!=null) {
				row.getCell(10).setCellType(HSSFCell.CELL_TYPE_FORMULA);
				row.getCell(10).setCellFormula("K"+ (z-3) + "+K"+ (z-2) + "+K"+ (z-1) + "+K"+ (z));
			}else {
				row.createCell(10).setCellType(HSSFCell.CELL_TYPE_FORMULA);
				row.getCell(10).setCellFormula("K"+ (z-3) + "+K"+ (z-2) + "+K"+ (z-1) + "+K"+ (z));
			}
			
			//二期
			if (row.getCell(12)!=null) {
				row.getCell(12).setCellType(HSSFCell.CELL_TYPE_FORMULA);
				row.getCell(12).setCellFormula("M"+ (z-3) + "+M"+ (z-2) + "+M"+ (z-1) + "+M"+ (z));
			}else {
				row.createCell(12).setCellType(HSSFCell.CELL_TYPE_FORMULA);
				row.getCell(12).setCellFormula("M"+ (z-3) + "+M"+ (z-2) + "+M"+ (z-1) + "+M"+ (z));
			}
			
			//内贸
			if (row.getCell(14)!=null) {
				row.getCell(14).setCellType(HSSFCell.CELL_TYPE_FORMULA);
				row.getCell(14).setCellFormula("O"+ (z-3) + "+O"+ (z-2) + "+O"+ (z-1) + "+O"+ (z));
			}else {
				row.createCell(14).setCellType(HSSFCell.CELL_TYPE_FORMULA);
				row.getCell(14).setCellFormula("O"+ (z-3) + "+O"+ (z-2) + "+O"+ (z-1) + "+O"+ (z));
			}
			
			//外贸
			if (row.getCell(16)!=null) {
				row.getCell(16).setCellType(HSSFCell.CELL_TYPE_FORMULA);
				row.getCell(16).setCellFormula("Q"+ (z-3) + "+Q"+ (z-2) + "+Q"+ (z-1) + "+Q"+ (z));
			}else {
				row.createCell(16).setCellType(HSSFCell.CELL_TYPE_FORMULA);
				row.getCell(16).setCellFormula("Q"+ (z-3) + "+Q"+ (z-2) + "+Q"+ (z-1) + "+Q"+ (z));
			}
			
			//全年合计
			if (row.getCell(18)!=null) {
				row.getCell(18).setCellType(HSSFCell.CELL_TYPE_FORMULA);
				row.getCell(18).setCellFormula("S"+ (z-3) + "+S"+ (z-2) + "+S"+ (z-1) + "+S"+ (z));
			}else {
				row.createCell(18).setCellType(HSSFCell.CELL_TYPE_FORMULA);
				row.getCell(18).setCellFormula("S"+ (z-3) + "+S"+ (z-2) + "+S"+ (z-1) + "+S"+ (z));
			}
			
			z = z + 5;
		}
        
        //总量
        int totalnum =yhxxList.size();
        if (sheet.getRow(totalnum*5+3)!=null) {
        	row = sheet.getRow(totalnum*5+3);
		}else {
			row = sheet.createRow(totalnum*5+3);
		} 
        
        //铁水
		if (row.getCell(2)!=null) {
			row.getCell(2).setCellType(HSSFCell.CELL_TYPE_FORMULA);
		}else {
			row.createCell(2).setCellType(HSSFCell.CELL_TYPE_FORMULA);
		}
		String concat = "C" + (totalnum*5+3);
		for(int m=totalnum-1;m>0;m--){
			concat+="+C"+ ((m)*5+3);
		}
		row.getCell(2).setCellFormula(concat);
		
		//水铁
		if (row.getCell(4)!=null) {
			row.getCell(4).setCellType(HSSFCell.CELL_TYPE_FORMULA);
		}else {
			row.createCell(4).setCellType(HSSFCell.CELL_TYPE_FORMULA);
		}
		concat = "E" + (totalnum*5+3);
		for(int m=totalnum-1;m>0;m--){
			concat+="+E"+ ((m)*5+3);
		}
		row.getCell(4).setCellFormula(concat);
		
		//滠口站
		if (row.getCell(6)!=null) {
			row.getCell(6).setCellType(HSSFCell.CELL_TYPE_FORMULA);
		}else {
			row.createCell(6).setCellType(HSSFCell.CELL_TYPE_FORMULA);
		}
		concat = "G" + (totalnum*5+3);
		for(int m=totalnum-1;m>0;m--){
			concat+="+G"+ ((m)*5+3);
		}
		row.getCell(6).setCellFormula(concat);
		
		//吴家山站
		if (row.getCell(8)!=null) {
			row.getCell(8).setCellType(HSSFCell.CELL_TYPE_FORMULA);
			row.getCell(8).setCellFormula("I"+ (z-3) + "+I"+ (z-2) + "+I"+ (z-1) + "+I"+ (z));
		}else {
			row.createCell(8).setCellType(HSSFCell.CELL_TYPE_FORMULA);
			row.getCell(8).setCellFormula("I"+ (z-3) + "+I"+ (z-2) + "+I"+ (z-1) + "+I"+ (z));
		}
		concat = "I" + (totalnum*5+3);
		for(int m=totalnum-1;m>0;m--){
			concat+="+I"+ ((m)*5+3);
		}
		row.getCell(8).setCellFormula(concat);
		
		//一期
		if (row.getCell(10)!=null) {
			row.getCell(10).setCellType(HSSFCell.CELL_TYPE_FORMULA);
			row.getCell(10).setCellFormula("K"+ (z-3) + "+K"+ (z-2) + "+K"+ (z-1) + "+K"+ (z));
		}else {
			row.createCell(10).setCellType(HSSFCell.CELL_TYPE_FORMULA);
			row.getCell(10).setCellFormula("K"+ (z-3) + "+K"+ (z-2) + "+K"+ (z-1) + "+K"+ (z));
		}
		concat = "K" + (totalnum*5+3);
		for(int m=totalnum-1;m>0;m--){
			concat+="+K"+ ((m)*5+3);
		}
		row.getCell(10).setCellFormula(concat);
		
		//二期
		if (row.getCell(12)!=null) {
			row.getCell(12).setCellType(HSSFCell.CELL_TYPE_FORMULA);
			row.getCell(12).setCellFormula("M"+ (z-3) + "+M"+ (z-2) + "+M"+ (z-1) + "+M"+ (z));
		}else {
			row.createCell(12).setCellType(HSSFCell.CELL_TYPE_FORMULA);
			row.getCell(12).setCellFormula("M"+ (z-3) + "+M"+ (z-2) + "+M"+ (z-1) + "+M"+ (z));
		}
		concat = "M" + (totalnum*5+3);
		for(int m=totalnum-1;m>0;m--){
			concat+="+M"+ ((m)*5+3);
		}
		row.getCell(12).setCellFormula(concat);
		
		//内贸
		if (row.getCell(14)!=null) {
			row.getCell(14).setCellType(HSSFCell.CELL_TYPE_FORMULA);
			row.getCell(14).setCellFormula("O"+ (z-3) + "+O"+ (z-2) + "+O"+ (z-1) + "+O"+ (z));
		}else {
			row.createCell(14).setCellType(HSSFCell.CELL_TYPE_FORMULA);
			row.getCell(14).setCellFormula("O"+ (z-3) + "+O"+ (z-2) + "+O"+ (z-1) + "+O"+ (z));
		}
		concat = "O" + (totalnum*5+3);
		for(int m=totalnum-1;m>0;m--){
			concat+="+O"+ ((m)*5+3);
		}
		row.getCell(14).setCellFormula(concat);
		
		//外贸
		if (row.getCell(16)!=null) {
			row.getCell(16).setCellType(HSSFCell.CELL_TYPE_FORMULA);
			row.getCell(16).setCellFormula("Q"+ (z-3) + "+Q"+ (z-2) + "+Q"+ (z-1) + "+Q"+ (z));
		}else {
			row.createCell(16).setCellType(HSSFCell.CELL_TYPE_FORMULA);
			row.getCell(16).setCellFormula("Q"+ (z-3) + "+Q"+ (z-2) + "+Q"+ (z-1) + "+Q"+ (z));
		}
		concat = "Q" + (totalnum*5+3);
		for(int m=totalnum-1;m>0;m--){
			concat+="+Q"+ ((m)*5+3);
		}
		row.getCell(16).setCellFormula(concat);
		
		//全年合计
		if (row.getCell(18)!=null) {
			row.getCell(18).setCellType(HSSFCell.CELL_TYPE_FORMULA);
			row.getCell(18).setCellFormula("S"+ (z-3) + "+S"+ (z-2) + "+S"+ (z-1) + "+S"+ (z));
		}else {
			row.createCell(18).setCellType(HSSFCell.CELL_TYPE_FORMULA);
			row.getCell(18).setCellFormula("S"+ (z-3) + "+S"+ (z-2) + "+S"+ (z-1) + "+S"+ (z));
		}
		concat = "S" + (totalnum*5+3);
		for(int m=totalnum-1;m>0;m--){
			concat+="+S"+ ((m)*5+3);
		}
		row.getCell(18).setCellFormula(concat);
		
		//占比
		z = 3;
		HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        cellStyle.setBorderBottom(CellStyle.BORDER_THIN); //下边框
        cellStyle.setBorderLeft(CellStyle.BORDER_THIN);//左边框
        cellStyle.setBorderTop(CellStyle.BORDER_THIN);//上边框
        cellStyle.setBorderRight(CellStyle.BORDER_THIN);//右边框
        for (int t=0;t<yhxxList.size()*5+1;t++) {
        	row = sheet.getRow(z);
        	//铁水
			if (row.getCell(3)!=null) {
				row.getCell(3).setCellStyle(cellStyle);
				row.getCell(3).setCellType(HSSFCell.CELL_TYPE_FORMULA);
				//row.getCell(3).setCellFormula("if(S"+(z+1)+"C"+ (z+1) +"/S"+ (z+1));
				row.getCell(3).setCellFormula("IF(("+"S"+ (z+1)+"=0),0," + "C"+ (z+1) +"/S"+ (z+1)+")");
				//IF((S7=0),0,C7/S7)
			}else {
				row.createCell(3).setCellType(HSSFCell.CELL_TYPE_FORMULA);
				row.getCell(3).setCellStyle(cellStyle);
/*				row.getCell(3).setCellFormula("C"+ (z+1) +"/S"+ (z+1));*/
				row.getCell(3).setCellFormula("IF(("+"S"+ (z+1)+"=0),0," + "C"+ (z+1) +"/S"+ (z+1)+")");
			}
			
			//水铁
			if (row.getCell(5)!=null) {
				row.getCell(5).setCellStyle(cellStyle);
				row.getCell(5).setCellType(HSSFCell.CELL_TYPE_FORMULA);
				row.getCell(5).setCellFormula("E"+ (z+1) +"/S"+ (z+1));
			}else {
				row.createCell(5).setCellType(HSSFCell.CELL_TYPE_FORMULA);
				row.getCell(5).setCellStyle(cellStyle);
				//row.getCell(5).setCellFormula("E"+ (z+1) +"/S"+ (z+1));
				row.getCell(5).setCellFormula("IF(("+"S"+ (z+1)+"=0),0," + "E"+ (z+1) +"/S"+ (z+1)+")");
			}
			
			//滠口站
			if (row.getCell(7)!=null) {
				row.getCell(7).setCellStyle(cellStyle);
				row.getCell(7).setCellType(HSSFCell.CELL_TYPE_FORMULA);
				row.getCell(7).setCellFormula("G"+ (z+1) +"/S"+ (z+1));
			}else {
				row.createCell(7).setCellType(HSSFCell.CELL_TYPE_FORMULA);
				row.getCell(7).setCellStyle(cellStyle);
				//row.getCell(7).setCellFormula("G"+ (z+1) +"/S"+ (z+1));
				row.getCell(7).setCellFormula("IF(("+"S"+ (z+1)+"=0),0," + "G"+ (z+1) +"/S"+ (z+1)+")");
			}
			
			//吴家山站
			if (row.getCell(9)!=null) {
				row.getCell(9).setCellStyle(cellStyle);
				row.getCell(9).setCellType(HSSFCell.CELL_TYPE_FORMULA);
				row.getCell(9).setCellFormula("I"+ (z+1) +"/S"+ (z+1));
			}else {
				row.createCell(9).setCellType(HSSFCell.CELL_TYPE_FORMULA);
				row.getCell(9).setCellStyle(cellStyle);
				//row.getCell(9).setCellFormula("I"+ (z+1) +"/S"+ (z+1));
				row.getCell(9).setCellFormula("IF(("+"S"+ (z+1)+"=0),0," + "I"+ (z+1) +"/S"+ (z+1)+")");
			}
			
			//一期
			if (row.getCell(11)!=null) {
				row.getCell(11).setCellStyle(cellStyle);
				row.getCell(11).setCellType(HSSFCell.CELL_TYPE_FORMULA);
				row.getCell(11).setCellFormula("K"+ (z+1) +"/S"+ (z+1));
			}else {
				row.createCell(11).setCellType(HSSFCell.CELL_TYPE_FORMULA);
				row.getCell(11).setCellStyle(cellStyle);
				//row.getCell(11).setCellFormula("K"+ (z+1) +"/S"+ (z+1));
				row.getCell(11).setCellFormula("IF(("+"S"+ (z+1)+"=0),0," + "K"+ (z+1) +"/S"+ (z+1)+")");
			}
			
			//二期
			if (row.getCell(13)!=null) {
				row.getCell(13).setCellStyle(cellStyle);
				row.getCell(13).setCellType(HSSFCell.CELL_TYPE_FORMULA);
				row.getCell(13).setCellFormula("M"+ (z+1) +"/S"+ (z+1));
			}else {
				row.createCell(13).setCellType(HSSFCell.CELL_TYPE_FORMULA);
				row.getCell(13).setCellStyle(cellStyle);
				//row.getCell(13).setCellFormula("M"+ (z+1) +"/S"+ (z+1));
				row.getCell(13).setCellFormula("IF(("+"S"+ (z+1)+"=0),0," + "M"+ (z+1) +"/S"+ (z+1)+")");
			}
			
			//内贸
			if (row.getCell(15)!=null) {
				row.getCell(15).setCellStyle(cellStyle);
				row.getCell(15).setCellType(HSSFCell.CELL_TYPE_FORMULA);
				row.getCell(15).setCellFormula("O"+ (z+1) +"/S"+ (z+1));
			}else {
				row.createCell(15).setCellType(HSSFCell.CELL_TYPE_FORMULA);
				row.getCell(15).setCellStyle(cellStyle);
				//row.getCell(15).setCellFormula("O"+ (z+1) +"/S"+ (z+1));
				row.getCell(15).setCellFormula("IF(("+"S"+ (z+1)+"=0),0," + "O"+ (z+1) +"/S"+ (z+1)+")");
			}
			
			//外贸
			if (row.getCell(17)!=null) {
				row.getCell(17).setCellStyle(cellStyle);
				row.getCell(17).setCellType(HSSFCell.CELL_TYPE_FORMULA);
				row.getCell(17).setCellFormula("Q"+ (z+1) +"/S"+ (z+1));
			}else {
				row.createCell(17).setCellType(HSSFCell.CELL_TYPE_FORMULA);
				row.getCell(17).setCellStyle(cellStyle);
				//row.getCell(17).setCellFormula("Q"+ (z+1) +"/S"+ (z+1));
				row.getCell(17).setCellFormula("IF(("+"S"+ (z+1)+"=0),0," + "Q"+ (z+1) +"/S"+ (z+1)+")");
			}
			z++;
        }
        
        //添加边框
		for(int m=3;m<=yhxxList.size()*5+3;m++){
			HSSFRow tempRow = sheet.getRow(m);
			for(int n=1;n<=18;n++){
				switch (n) {
				case 3:
					continue;
				case 5:
					continue;
				case 7:
					continue;
				case 9:
					continue;
				case 11:
					continue;
				case 13:
					continue;
				case 15:
					continue;
				case 17:
					continue;
				default:
					break;
				}
				if (tempRow.getCell(n)!=null) {
					tempRow.getCell(n).setCellStyle(headStyle);
				}else {
					tempRow.createCell(n).setCellStyle(headStyle);
				}
			}
		}
			
		HSSFRow  sixthrow = sheet.createRow(sheet.getLastRowNum()+1);
		HSSFCell r_czrcell = sixthrow.createCell(17);
		r_czrcell.setCellValue("操作人:");
		HSSFCell r_czrvalue = sixthrow.createCell(18);
		r_czrvalue.setCellValue(getBackendOperator(request).getRealName());
		
		workbook.write(outputStream);  
        outputStream.flush();  
        outputStream.close(); 
		
		return null;  
	}
	
	/**
	 * 查询分类统计验证
	 * 
	 * @date 2016年6月26日 下午12:44:40
	 * @param yhxxDTO
	 * @return
	 * @throws Exception
	 *             Result
	 */
	@ResponseBody
	@RequestMapping("flreportyz")
	public Result flreportyz(HttpServletRequest request,
			@ModelAttribute SbhzinfoDTO sbhzinfoDTO) throws Exception {
        
		Result result = new Result(true);
		Integer status = 0;
		
		sbhzinfoDTO.setHzzt(3);
		List<YhxxVO> yhxxList = isbhzinfoService.getCompanyList(sbhzinfoDTO);
		if (yhxxList.size()==0) {
			status = -1;
		}
		
		result.addAttribute("status", status);
		return result;
	}
	
	/**
	 * 审核记录表明细列表
	 * 
	 * @date 2016年6月26日 上午11:29:07
	 * @param SbhzinfoDTO
	 * @return
	 * @throws Exception
	 *             Pagination<SbhzinfoVO>
	 */
	@ResponseBody
	@RequestMapping("shreportmxlist")
	public Pagination<SbmxinfoVO> shreportmxlist(@ModelAttribute SbmxinfoDTO sbmxinfoDTO)
			throws Exception {
		sbmxinfoDTO.setSfsc(0);
		if (sbmxinfoDTO.getSort()!=null) {
			sbmxinfoDTO.setSort(sbmxinfoDTO.getSort().concat(" " + sbmxinfoDTO.getOrder()));
		}else {
			sbmxinfoDTO.setSort("id");
		}
		
		return isbmxinfoService.getListVO(sbmxinfoDTO,mainDomain + "/upload/pictures/");
	}
	
	/**
	 * 导出审核记录数据Excel
	 * 
	 * @return
	 */
	@RequestMapping(value = "/export/shreportExcelView", method = RequestMethod.POST)
	public ModelAndView shreportExcelView(@ModelAttribute SbhzinfoDTO sbhzinfoDTO) throws Exception {
		
		sbhzinfoDTO.setShreportzt("1");
		List<SbhzinfoVO> list = isbhzinfoService.getListVO(sbhzinfoDTO).getRows();
		List<SbmxinfoVO> mxList = new ArrayList<SbmxinfoVO>();
		SbmxinfoDTO sbmxinfoDTO = new SbmxinfoDTO();
		for (SbhzinfoVO sbhzinfoVO : list) {
			sbmxinfoDTO.setSfsc(0);
			sbmxinfoDTO.setHzdh(sbhzinfoVO.getHzdh());
			List<SbmxinfoVO> templist = isbmxinfoService.queryList(sbmxinfoDTO);
			if (sbhzinfoVO.getHzzt()==4) {
				for (SbmxinfoVO sbmxinfoVO : templist) {
					if (sbmxinfoVO.getJjyy()==null||"".equals(sbmxinfoVO.getJjyy())) {
						sbmxinfoVO.setJjyy("不通过");
					}
				}
			}
			mxList.addAll(templist);
		}
		
		Map<String, Object> model = new HashMap<String, Object>();
		model.put("list", mxList);
		model.put("name", getBackendOperator(request).getRealName());
		
		return new ModelAndView(new shreportViewExcel(), model);
	}
	
	/**
	 * 查询预览列表
	 * 
	 * @date 2016年6月26日 上午11:29:07
	 * @param SbhzinfoDTO
	 * @return
	 * @throws Exception
	 *             Pagination<SbhzinfoVO>
	 */
	@ResponseBody
	@RequestMapping("previewlist")
	public Result previewlist(@ModelAttribute SbhzinfoDTO sbhzinfoDTO)
			throws Exception {
		
		List<PreviewinfoVO> list = new ArrayList<PreviewinfoVO>();
		PreviewinfoVO previewinfoVO = null;
		
		List<YhxxVO> yhxxList = isbhzinfoService.getCompanyList(sbhzinfoDTO);
		for (YhxxVO yhxxVO : yhxxList) {
			sbhzinfoDTO.setYhid(yhxxVO.getYhid());
			for(int i=1;i<=5;i++){
				if (i<=4) {
					sbhzinfoDTO.setSbjd(i);
				}else {
					sbhzinfoDTO.setSbjd(null);
				}
				previewinfoVO = isbhzinfoService.getPreviewList(sbhzinfoDTO);
				if (previewinfoVO==null) {
					previewinfoVO = new PreviewinfoVO();
					previewinfoVO.setSbjd(String.valueOf(i));
					previewinfoVO.setQyqc(yhxxVO.getQyqc());
				}
				if (i==5) {
					previewinfoVO.setSbjd("小计");
				}
				list.add(previewinfoVO);
			}
		}
		
		//总量
		sbhzinfoDTO.setSbjd(null);
		sbhzinfoDTO.setYhid(null);
		previewinfoVO = isbhzinfoService.getPreviewList(sbhzinfoDTO);
		if (previewinfoVO!=null) {
			previewinfoVO.setQyqc("总量");
			previewinfoVO.setSbjd("");
			list.add(previewinfoVO);
		}
				
		Result result = new Result(true);
		result.addAttribute("list", list);
		
		return result;
	}
	
	/**
	 * 更新时间错误列表
	 * 
	 * @date 2016年6月26日 上午11:29:07
	 * @param SbhzinfoDTO
	 * @return
	 * @throws Exception
	 *             Pagination<SbhzinfoVO>
	 */
	@ResponseBody
	@RequestMapping("updateWrongTimeList")
	public Result updateWrongTimeList(@RequestBody List<SbmxinfoVO> list)
			throws Exception {
		
		isbhzinfoService.updateWrongTimeList(list);
				
		Result result = new Result(true);
		result.addAttribute("status", 0);
		
		return result;
	}
	
	/**
	 * 更新所有该季度的数据为通过
	 * 
	 * @date 2016年6月26日 上午11:29:07
	 * @param SbhzinfoDTO
	 * @return
	 * @throws Exception
	 *             Pagination<SbhzinfoVO>
	 */
	@ResponseBody
	@RequestMapping("crossAllHz")
	public Result crossAllHz(@ModelAttribute SbhzinfoDTO sbhzinfoDTO)
			throws Exception {
		
		isbhzinfoService.crossAllHz(sbhzinfoDTO);
				
		Result result = new Result(true);
		result.addAttribute("status", 0);
		
		return result;
	}
	
	/**
	 * 更新材料审核
	 * 
	 * @date 2016年6月26日 上午11:29:07
	 * @param SbhzinfoDTO
	 * @return
	 * @throws Exception
	 *             Pagination<SbhzinfoVO>
	 */
	@ResponseBody
	@RequestMapping("crosscl")
	public Result crosscl(@ModelAttribute SbhzinfoDTO sbhzinfoDTO)
			throws Exception {
		
		SbhzinfoModel sbhzinfoModel = new SbhzinfoModel();
		BeanUtils.copyProperties(sbhzinfoDTO,sbhzinfoModel);
		sbhzinfoModel.setGxsj(new Date());
		isbhzinfoService.updateByPrimaryKey(sbhzinfoModel);
				
		Result result = new Result(true);
		result.addAttribute("status", 0);
		
		return result;
	}
	
	/**
	 * 导出集装箱铁水联运专项补贴汇总表
	 * 
	 * @return
	 */
	@RequestMapping("/export/bttotalExcelView")
	public ModelAndView bttotalExcelView(@ModelAttribute SbhzinfoDTO sbhzinfoDTO) throws Exception {
		
		sbhzinfoDTO.setHzzt(3);
		List<SbhzinfoVO> list = isbhzinfoService.getListVO(sbhzinfoDTO).getRows();
		
		Map<String, Object> model = new HashMap<String, Object>();
		model.put("list", list);
		model.put("sbnf", sbhzinfoDTO.getSbnf());
		model.put("sbjd", sbhzinfoDTO.getSbjd());
		
		return new ModelAndView(new BtTotalViewExcel(), model);
	}

	@Resource(name = "isbhzinfoService")
	private ISbhzinfoService isbhzinfoService;
	
	@Resource(name = "isbmxinfoService")
	private ISbmxinfoService isbmxinfoService;
	
	@Resource(name = "ijzxtlinfoService")
	private IJzxtlinfoService ijzxtlinfoService;
	
	@Resource(name = "ijzxslinfoService")
	private IJzxslinfoService ijzxslinfoService;
}
